<?php
/**
 * 
 * @author Paolo Bertuzzo
 *
 */
namespace Catalog\Data;

class CategoryDao extends AbstractDao
{
    
    /**
     * 
     * @param string $language
     */
    public function getByLanguage($language)
    {
        $query = $this->entityManager->createQuery('select c, lct from Catalog\Entity\Category c, ' .
            'Catalog\Entity\LocalizedCategoryText lct where lct.language = ?1 and lct.type in (\'name\', \'alias\') and c.categoryId = lct.categoryId order by c.path asc, lct.type desc');
        
        $query->setParameter(1, $language);
        $query->useResultCache(true);
        
        return $query->getResult();
    }    

    /**
     * 
     * @param string $categoryAlias
     * @param string $language
     */
    public function getNameByAliasAndLanguage($categoryAlias, $language)
    {
    	$query = $this->entityManager->createQuery('select lct from Catalog\Entity\LocalizedCategoryText lct where lct.language = ?1 and lct.type = \'name\' ' .
    	    ' and lct.categoryId in (select lct1.categoryId from Catalog\Entity\LocalizedCategoryText lct1 where lct1.language = ?1 and lct1.type = \'alias\' and lct1.text = ?2)');
    
    	$query->setParameter(1, $language);
    	$query->setParameter(2, $categoryAlias);
    	$query->useResultCache(true);
    
    	return $query->getResult();
    }
    
    /**
     * 
     * @param string $categoryAlias
     * @param string $language
     * @param int $storeId
     * @param int $offset
     * @param int $maxResults
     */
    public function getProductsByCategoryAndLanguage($categoryAlias, $language, $storeId, $offset, $maxResults)
    {
     	$query = $this->entityManager->createQuery('select p, lpt_name, lpt_alias, pi, pp from Catalog\Entity\Product p, Catalog\Entity\ProductCategory pc, ' .
    	    'Catalog\Entity\LocalizedProductText lpt_name, Catalog\Entity\LocalizedProductText lpt_alias, Catalog\Entity\ProductImage pi, Catalog\Entity\ProductPrice pp where pc.categoryId in (select lct.categoryId from Catalog\Entity\LocalizedCategoryText lct ' .
    	    'where lct.type = \'alias\' and lct.language = ?2 and lct.text = ?1) and pc.productId = p.productId and lpt_name.language = ?3 and lpt_name.type = \'name\' and lpt_alias.language = ?4 and lpt_alias.type = \'alias\'' . 
    	    'and p.productId = lpt_name.productId and p.productId = lpt_alias.productId and p.productId = pi.productId and pi.language = ?5 and pi.type = \'default\' ' . 
    	    'and p.productId = pp.productId and pp.storeId = ?6 order by p.code asc');
            
    	$query->setParameter(1, $categoryAlias);
    	$query->setParameter(2, $language);
    	$query->setParameter(3, $language);
    	$query->setParameter(4, $language);
    	$query->setParameter(5, $language);
    	$query->setParameter(6, $storeId);
    	 
    	// Perform pagination
    	$query->setFirstResult($offset);
    	$query->setMaxResults($maxResults);
    	$query->useResultCache(true);
    
    	return $query->getResult();
    }
    
    /**
     * 
     * @param string $categoryAlias
     * @param string $language
     */
    public function countProductsByCategory($categoryAlias, $language) 
    {
        $query = $this->entityManager->createQuery('select count(pc) from Catalog\Entity\ProductCategory pc ' .
        		'where pc.categoryId in (select lct.categoryId from Catalog\Entity\LocalizedCategoryText lct ' .
        		'where lct.type = \'alias\' and lct.language = ?2 and lct.text = ?1)');
        
    	$query->setParameter(1, $categoryAlias);
    	$query->setParameter(2, $language);
    	$query->useResultCache(true);
    	
    	return $query->getSingleScalarResult();;
    }
    
}

?>